InfyTQ Subquery Quiz 1

-- create a table

CREATE TABLE gift (

giftid INTEGER PRIMARY KEY,

giftname TEXT,

price integer,

discount integer,

availability integer

);



-- insert some values

INSERT INTO gift VALUES (101, 'Magic Mug', 500,9,10);

INSERT INTO gift VALUES (102, 'Golfset', 3550,5,15);

INSERT INTO gift VALUES (103, 'Little Astroner', 2000,20,18);

INSERT INTO gift VALUES (104, 'Paintings', 1500,15,15);

INSERT INTO gift VALUES (105, 'French Gastonomy', 3000,7,10);

INSERT INTO gift VALUES (106, 'Magic set', 1300,13,13);



SELECT g1.giftid, g1.giftname FROM gift g1 JOIN gift g2 ON g1.availability = g2.availability AND g1.discount <> g2.discount WHERE g1.discount>7;

101|Magic Mug

104|Paintings

-- create a table

CREATE TABLE gift (

giftid INTEGER PRIMARY KEY,



giftname TEXT,

price integer,

discount integer,

availability integer

);



-- insert some values

INSERT INTO gift VALUES (101, 'Magic Mug', 500,9,10);

INSERT INTO gift VALUES (102, 'Golfset', 3550,5,15);

INSERT INTO gift VALUES (103, 'Little Astroner', 2000,20,18);

INSERT INTO gift VALUES (104, 'Paintings', 1500,15,15);

INSERT INTO gift VALUES (105, 'French Gastonomy', 3000,7,10);

INSERT INTO gift VALUES (106, 'Magic set', 1300,13,13);

Select max(G1.price) from gift G1 WHERE 2=( SELECT COUNT (DISTINCT price) FROM Gift G2 where G1.price < G2.price);

2000

-- create a table

CREATE TABLE instructor (

instructorid INTEGER,

name TEXT,

subject TEXT,

university TEXT,

salary integer

);



-- insert some values



INSERT INTO instructor VALUES (1201, 'Alexa', 'Java','Harvard',70000);

INSERT INTO instructor VALUES (1202, 'Sam', 'Ruby' ,'Oxford', 7500);

INSERT INTO instructor VALUES (1201, 'Alex', 'Rdbms', 'Harvard', 60000);

INSERT INTO instructor VALUES (1203, 'Mitchel ', 'Networking', 'Cambridge', 50000);

INSERT INTO instructor VALUES (1202, 'Sam', 'Rdbms' ,'Harvard', 40000);

INSERT INTO instructor VALUES (1203, 'Mitchel', '.Net', 'Oxford', 50000);

SELECT instructorid, name FROM instructor WHERE salary > 40000

GROUP BY instructorid, name HAVING COUNT (DISTINCT university) > 1;

-- create a table

CREATE TABLE instructor (

instructorid INTEGER,

name TEXT,

subject TEXT,

university TEXT,

salary integer

);



-- insert some values



INSERT INTO instructor VALUES (1201, 'Alexa', 'Java','Harvard',70000);

INSERT INTO instructor VALUES (1202, 'Sam', 'Ruby' ,'Oxford', 7500);

INSERT INTO instructor VALUES (1201, 'Alex', 'Rdbms', 'Harvard', 60000);

INSERT INTO instructor VALUES (1203, 'Mitchel ', 'Networking', 'Cambridge', 50000);

INSERT INTO instructor VALUES (1202, 'Sam', 'Rdbms' ,'Harvard', 40000);

INSERT INTO instructor VALUES (1203, 'Mitchel', '.Net', 'Oxford', 50000);

SELECT ins.name from instructor ins join instructor ins1 on ins.instructorid=ins1.instructorid where ins1.university= ins.university;

Alexa

Alexa

Sam

Alex

Alex

Mitchel 

Sam

Mitchel